﻿/***********************************************************************************************************************************
 *    Name: DeadBlocks.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 2012+
 *	http://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/
**/

-- create the session
BEGIN TRY DROP EVENT SESSION DeadBlocks ON SERVER END TRY BEGIN CATCH END CATCH;
DECLARE @xe_create NVARCHAR(MAX)= N'CREATE EVENT SESSION [DeadBlocks] ON SERVER
ADD EVENT sqlserver.blocked_process_report (
	ACTION(sqlserver.client_app_name, sqlserver.client_hostname)),
ADD EVENT sqlserver.xml_deadlock_report (
	ACTION(sqlserver.client_app_name, sqlserver.client_hostname))
--ADD TARGET package0.event_file (SET filename= N''' + master.DBAdmin.GetInstancePath(N'ErrorDumpDir') + N'DeadBlocks.xel'')
ADD TARGET package0.ring_buffer
WITH (EVENT_RETENTION_MODE= ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY= 6 SECONDS);';
PRINT @xe_create; EXECUTE (@xe_create);
GO

-- start the session
EXECUTE dbo.sp_configure 'blocked process threshold', 6; RECONFIGURE;
ALTER EVENT SESSION DeadBlocks ON SERVER STATE= START;
GO

-- read the session target
CREATE VIEW DBAdmin.RingBuffer AS
WITH
  ring_buffer (name, target_data) AS (
	SELECT name, CAST(target_data AS XML)
	  FROM sys.dm_xe_sessions INNER JOIN sys.dm_xe_session_targets ON (address = event_session_address)
	  WHERE target_name = N'ring_buffer'),
  event_data (name, event_data) AS (
	SELECT name, tdn.xevent.query('.') FROM ring_buffer CROSS APPLY target_data.nodes('/RingBufferTarget/event') AS tdn(xevent))
SELECT name, event_data FROM event_data;

SELECT
	xe.name,
	xe.event_data.value('(event/@timestamp)[1]', 'datetime2') AS timestamp,
	xe.event_data.value('(event/@name)[1]', 'nvarchar(255)') AS event_name,
	event_data
  FROM DBAdmin.RingBuffer xe WHERE xe.name = N'DeadBlocks'
GO

-- stop the session
ALTER EVENT SESSION DeadBlocks ON SERVER STATE= STOP; 
EXECUTE dbo.sp_configure 'blocked process threshold', 0; RECONFIGURE;
GO





WITH events_cte AS (
  SELECT
    xevents.event_data,
    DATEADD(mi,
    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
    xevents.event_data.value(
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
    xevents.event_data.value(
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
    xevents.event_data.value(
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
    xevents.event_data.query(
      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
      AS blocked_process_report,
    xevents.event_data.query(
      '(event/data[@name="xml_report"]/value/deadlock)[1]')
      AS deadlock_graph
  FROM    (SELECT * FROM DBAdmin.RingBuffer xe WHERE xe.name = N'DeadBlocks') as xevents
)
SELECT
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name]
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  index_id,
  [duration (ms)],
  lock_mode,
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;
